Relational databases are widely used in industry to store, inter alia, commercial data, such as client details and product details. A Relational Database Management System (RDBMS) is software which enables a user to define, construct and manipulate a database using a high level language. Examples of RDBMS's are DB2® of IBM®, Informix® Dynamic Server™, Microsoft® SQL Server and Oracle®.
Structured Query Language (SQL) is a standard high level language, specified by the American National Standards Institute (ANSI), and provides for data definition and manipulation. It is provided by most RDBMS providers, although each provider may provide extensions to the basic standard language. Probably the most widely used SQL statement is the “Select” statement which is used to retrieve data from a database. This is probably best illustrated by a simple example. Consider a table named “EmployeeTable”, defined in a database, which contains 3 rows of 3 columns of data items, each row containing data items containing the name, number and salary of each employee. An example of the contents of such a database are:
Name (varchar(20))Number (char(7))Salary (integer)Alexandra001234535,000Sean002234630,000Rebecca003234730,000Note that each column has a defined data type: “Name”, defined as Varchar(20), is a variable length null terminated character string of up to 20 characters; “Number”, defined as char(7), is a fixed length character string of 7 bytes; and “Salary”, defined as integer, is a number.
A “Select” statement can now be used to retrieve data items from this table. For example, the simplest form of the “Select” statement is:
Select * From EmployeeTable;
This is used to obtain a list of all data items in the table. The exact format of the output may vary according to which RDBMS is used but may, for example, be:
NameNumberSalarySean002234630,000Alexandra001234535,000Rebecca003234730,000
While this style of output is not necessarily produced by any RDBMS it will be used for the remainder of this specification by way of example only.
An example of a more selective “Select” statement is:
Select Name, Salary From EmployeeTable Where Salary>32000;
This would return the name and salary of all employees with a salary of more than 32,000. Note, however, that this statement requires some knowledge of the “EmployeeTable” as the names of the columns “Name” and “Salary” are specified. In this example the output would be:
NameSalaryAlexandra35,000
Now, suppose that the salary output is required in US Dollars although the “Salary” column in “EmployeeTable” is entered in sterling. One way of achieving this is for the user to define, to the RDBMS, a function for converting sterling to US dollars which can be used as part of a select statement. Such a function is known as a User Defined Function (UDF) in DB2. This function could, for example, be called “Sterling_to_USDollars”, take as input an integer (the data type of “Salary”), and output the resulting calculation as a string containing “$” followed by the converted salary. It would then be possible to specify a “Select” statement using this function:
Select Name, Sterling_to_USDollars (Salary) From EmployeeTable
                Where Salary >32000;This statement, assuming an exchange rate of 1.4, would then produce output:        
NameSalaryAlexandra$49,000
Further, suppose the company uses the last four characters of the “Number” column to contain the telephone extension of each employee, and requires an output to contain the telephone extension instead of the employee number. This can also be done using a function defined to the RDBMS which could, for example, be called “Number_to_Phone”, take as input a 7 character field, and output the last 4 characters of the “Number” field preceded by an “x”. This now enables a select statement of:
Select Name, Number_to_Phone (Number), Sterling_to_USDollars (Salary)
                From EmployeeTable;This would produce the output:        
NameNumberSalarySeanx2346$42,000Alexandrax2345$49,000Rebeccax2347$42,000
Note that it is possible to combine the functionality of “Sterling_to_USDollars” and “Number_to_Phone” into a single function taking two input parameters (“Salary” and “Number”).
It can be noted from these examples that a “Select” statement which is more selective than “Select *”, requires some knowledge of “EmployeeTable”. For example the user must know the column names (“Name”, “Number”, “Salary”) and that data type of “Salary” (for “Where Salary>32000”). Further UDF's require some knowledge of the data type taken as input, for example “Sterling_to_USDollars” must be written to accept the “Salary” input as an integer.
In fact any method of producing output which is not a full and unmodified listing of the database as produced from “Select *” requires some knowledge of the database. Whilst such knowledge can be removed from the select statement by writing a UDF function that takes an entire row as input, the UDF must be written with a parameter list that defines the correct column data types in the correct order. This is clearly restrictive on the user, making it impossible to write a UDF that is not hard coded to accept a specific data type or specific data types in a specific order.
Therefore, there is a need for a more flexible method and system for retrieving data using a UDF. The present invention provides such a method and system.